"""
Python操作MySQL数据库
pymysql     -- pip install pymysql

1、导包
2、创建连接【连接地址、用户名、密码、数据库名、端口：3306、编码集：utf-8】
3、创建游标
4、准备要执行的SQL语句
5、执行SQL语句
6、关闭游标
7、关闭连接

异常捕获
try：
    要监控的代码    --- 这里的代码是一定会执行的，这里代码不一定会有异常
except 异常的具体类型：
    出现异常时给出的解决方案或提示信息      --- 这里的是有异常时才会执行，没有异常不会执行
except 异常的具体类型：
    出现异常时给出的解决方案或提示信息      --- 这里的是有异常时才会执行，没有异常不会执行
except 异常的具体类型：
    出现异常时给出的解决方案或提示信息      --- 这里的是有异常时才会执行，没有异常不会执行
finally：
    不管监控的代码有没有出现异常，这里的代码时都会执行的

with 表达式 as 变量：
    语句执行体


"""
# # 1、导包
# import pymysql
# # 2、创建连接【连接地址、用户名、密码、数据库名、端口：3306、编码集：utf-8】
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# # 3、创建游标
# cursor = db.cursor()
# # 4、准备要执行的SQL语句
# sql = f"select * from t_dept where deptno=%s"
# d = (30,)
# # 5、执行SQL语句
# cursor.execute(sql, d)
# data = cursor.fetchall()
# print(data,type(data))
# for row in data:
#     print(row)
# # 6、关闭游标
# cursor.close()
# # 7、关闭连接
# db.close()

# # 1、导包
# import pymysql
# # 2、创建连接【连接地址、用户名、密码、数据库名、端口：3306、编码集：utf-8】
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# # 3、创建游标
# cursor = db.cursor()
# # 4、准备要执行的SQL语句
# # sql = "insert into t_dept values (41,'人事部','郑州')"
# data = ("(42,'人事部','郑州'),"
#         "(43,'人事部','郑州'),"
#         "(44,'人事部','郑州')")
# sql = f"insert into t_dept values {data}"
# # 5、执行SQL语句
# cursor.execute(sql)
# db.commit()
# # 6、关闭游标
# cursor.close()
# # 7、关闭连接
# db.close()
# print(sql)

# # 1、导包
# import pymysql
# # 2、创建连接【连接地址、用户名、密码、数据库名、端口：3306、编码集：utf-8】
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# # 3、创建游标
# cursor = db.cursor()
# # 4、准备要执行的SQL语句
# sql = "update t_dept set loc='北京' where deptno=44"
# # 5、执行SQL语句
# cursor.execute(sql)
# db.commit()
# # 6、关闭游标
# cursor.close()
# # 7、关闭连接
# db.close()
# print(sql)

# # 1、导包
# import pymysql
# # 2、创建连接【连接地址、用户名、密码、数据库名、端口：3306、编码集：utf-8】
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# # 3、创建游标
# cursor = db.cursor()
# # 4、准备要执行的SQL语句
# sql = "delete from t_dept where deptno=44"
# # 5、执行SQL语句
# cursor.execute(sql)
# db.commit()
# # 6、关闭游标
# cursor.close()
# # 7、关闭连接
# db.close()
# print(sql)

# import pymysql
#
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# try:
#     with db.cursor() as cursor:
#         sql = "select * from t_dept"
#         cursor.execute(sql)
#         data = cursor.fetchall()
#         for i in data:
#             print(i)
# except Exception as e:
#     print('数据查询失败！',e)
# finally:
#     db.close()

# import pymysql
#
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# try:
#     with db.cursor() as cursor:
#         sql = "insert into t_dept values (44,'测试部','上海')"
#         cursor.execute(sql)
#         db.commit()
#         print('数据插入成功！')
# except Exception as e:
#     print('数据插入失败！',e)
# finally:
#     db.close()

# import pymysql
#
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# try:
#     with db.cursor() as cursor:
#         sql = "update t_dept set loc='深圳' where deptno=44"
#         cursor.execute(sql)
#         db.commit()
#         print('数据更新成功！')
# except Exception as e:
#     print('数据更新失败！',e)
# finally:
#     db.close()

# import pymysql
#
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='root',
#     db='company',
#     port=3306,
#     charset='utf8'
# )
# try:
#     with db.cursor() as cursor:
#         sql = "delete from t_dept where deptno=43"
#         r = cursor.execute(sql)
#         db.commit()
#         print(r)
#         print('数据删除成功！')
# except Exception as e:
#     print('数据删除失败！',e)
# finally:
#     db.close()


from pymysql import Connection

conn = None
try:
    conn = Connection(
        host='localhost',
        user='root',
        password='123456',
        database='db_py',
        port=3306
    )
    cursor = conn.cursor()
    sql = '''
    create table if not exists person(
	username  varchar(20),
	age   int,
	sex  char(4),
	high  int
)
'''
    data = (
        ('张三', 20, '男', 170),
        ('李四', 25, '女', 160),
        ('王五', 30, '男', 180),
        ('赵六', 28, '女', 175),
        ('孙琪', 35, '男', 185),
        ('李磊', 22, '女', 165),
        ('小明', 23, '男', 170),
        ('李华', 26, '女', 168),
        ('陈立', 24, '男', 172),
        ('周杰', 27, '女', 167)
    )

    for entry in data:
        sql2 = "INSERT INTO person(username, age, sex, high) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql2, entry)

    conn.commit()
    print('数据插入成功！')

except Exception as e:
    print(e)
finally:
    if 'cursor' in locals():  # 检查 cursor 是否被定义
        cursor.close()
    if conn:
        conn.close()





